﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace LiteExpress.Core.DbClient
{
    public class DbInfoUtilities
    {
        public class ColumnInfo
        {
            public string Name { get; set; }
            public string DbType { get; set; }
            public string DotnetType { get; set; }
            public string IsPrimary { get; set; }
            public bool IsNullable { get; set; }
        }

        public static IEnumerable<string> GetTables(string dbName)
        {
            var db = DbFactory.GetObjectDB(dbName);
            string sql = string.Empty;
            switch (db.DBType)
            {
                case DataBaseType.MsSql:
                    sql = "select name from Sysobjects where xtype='U' order by name; ";
                    break;
                case DataBaseType.MySql:
                    sql = "SHOW TABLES;";
                    break;
                case DataBaseType.Oracle:
                    throw new NotImplementedException();                    
                case DataBaseType.Access:
                    throw new NotImplementedException();
                default:
                    sql = "select name from Sysobjects where xtype='U' order by name; ";
                    break;
            }

            return DbFactory.GetConnection(db.DBName, (conn) =>
            {
                return conn.Query<string>(sql);
            });
        }

        public static IEnumerable<ColumnInfo> GetColumns(string dbName, string tableName)
        {
            var db = DbFactory.GetObjectDB(dbName);
            
            switch (db.DBType)
            {
                case DataBaseType.MsSql:
                    return GetMsSqlColumns(db, tableName);
                case DataBaseType.MySql:
                    return GetMySqlColumns(db, tableName);
                case DataBaseType.Oracle:
                    return GetOracleColumns(db, tableName);
                case DataBaseType.Access:
                    return GetAccessColumns(db, tableName);
                default:
                    return GetMsSqlColumns(db, tableName);
            }
        }

        private static IEnumerable<ColumnInfo> GetMsSqlColumns(DataBase db, string tableName)
        {
            string sql = @"SELECT a.name as Name
, IsPrimary=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in ( 
SELECT name FROM sysindexes WHERE indid in( 
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end, 
DbType=b.name,
IsNullable=case when a.isnullable=1 then 'yes'else 'no' end
FROM syscolumns a 
left join systypes b on a.xtype=b.xusertype 
inner join sysobjects d on a.id=d.id and (d.xtype='U' or d.xtype='V') and d.name<>'dtproperties' 
where d.name=@TableName order by a.colorder ";

            var result =  DbFactory.GetConnection(db.DBName, (conn) =>
            {
                return conn.Query<ColumnInfo>(sql, new { TableName = tableName });
            });

            foreach (var item in result)
            {
                switch (item.DbType)
                {
                    case "bigint":
                        item.DotnetType = "long";
                        break;
                    case "bit":
                        item.DotnetType = "bool";
                        break; 
                    case "binary":
                        item.DotnetType = "object";
                        break;
                    case "datetime":
                    case "smalldatetime":
                        item.DotnetType = "DateTime";
                        break;
                    case "float":
                        item.DotnetType = "double";
                        break;
                    case "image":
                    case "timestamp":
                    case "varbinary":
                        item.DotnetType = "byte[]";
                        break;
                    case "money":
                    case "smallmoney":
                    case "numeric":                    
                        item.DotnetType = "decimal";
                        break; 
                    case "real":
                        item.DotnetType = "float";
                        break;
                    case "smallint":
                        item.DotnetType = "short";
                        break;
                    case "char":
                    case "nchar":
                    case "varchar":                        
                    case "nvarchar":
                    case "text":
                    case "ntext":
                        item.DotnetType = "string";
                        break; 
                    case "tinyint":
                        item.DotnetType = "byte";
                        break;
                    case "uniqueidentifier":
                        item.DotnetType = "Guid";
                        break;
                    default:
                        item.DotnetType = item.DbType;
                        break;
                }
            }

            return result;
        }

        private static IEnumerable<ColumnInfo> GetMySqlColumns(DataBase db, string tableName)
        {
            return GetMsSqlColumns(db, tableName);
        }

        private static IEnumerable<ColumnInfo> GetOracleColumns(DataBase db, string tableName)
        {
            throw new NotImplementedException();
        }

        private static IEnumerable<ColumnInfo> GetAccessColumns(DataBase db, string tableName)
        {
            throw new NotImplementedException();
        }
    }
}
